/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;
import com.mysql.jdbc.CallableStatement;
import dbhelper.dbconnect;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import POJO.sanPhamPOJO;
/**
 *
 * @author SoftVnn Member
 */
public class sanPhamDAO {
    public ArrayList<sanPhamPOJO> getAll()
    {
        ArrayList<sanPhamPOJO> arr = new ArrayList<sanPhamPOJO>();
        try
        {
            dbconnect con = new dbconnect();
            con.open();
            String sql = String.format("call spSanPham_getAll();");
            CallableStatement st = (CallableStatement) con.getConn().prepareCall(sql);
            st.execute();
            ResultSet rs = st.getResultSet();
            while (rs.next())
            {
                sanPhamPOJO obj = new sanPhamPOJO();
                obj.setMaSanPham(rs.getString("MaSanPham"));
                obj.setMaloaiSanPham(rs.getString("MaLoaiSanPham"));
                obj.setSoLuong(rs.getInt("SoLuong"));
                obj.setGiaBan(rs.getInt("GiaBan"));
                obj.setMaNhaSanXuat(rs.getString("MaNhaSanXuat"));
                obj.setTenSanPham(rs.getString("TenSanPham"));
                arr.add(obj);
            }
            con.close();
        }
        catch(SQLException ex)
        {
            //error;
        }
        return arr;
    }
    public ArrayList<sanPhamPOJO> get(String MaSanPham)
    {
        ArrayList<sanPhamPOJO> arr = new ArrayList<sanPhamPOJO>();
        try
        {           
            dbconnect con = new dbconnect();
            con.open();
            String sql = String.format("call spSanPham_get('%s');", MaSanPham);
            CallableStatement st = (CallableStatement) con.getConn().prepareCall(sql);
            st.execute();
            ResultSet rs = st.getResultSet();
            while (rs.next())
            {
                sanPhamPOJO obj = new sanPhamPOJO();
                obj.setMaSanPham(rs.getString("MaSanPham"));
                obj.setMaloaiSanPham(rs.getString("MaLoaiSanPham"));
                obj.setSoLuong(rs.getInt("SoLuong"));
                obj.setGiaBan(rs.getInt("GiaBan"));
                obj.setMaNhaSanXuat(rs.getString("MaNhaSanXuat"));
                obj.setTenSanPham(rs.getString("TenSanPham"));
                arr.add(obj);
            }
            con.close();
        }
        catch(SQLException ex)
        {
            //error
        }
        return arr;
    }
    public int insert(String MaSanPham, String MaLoaiSanPham, int SoLuong, int GiaBan, String MaNhaSanXuat, String TenSanPham)
    {
        int result = 0;
        try{
            dbconnect con = new dbconnect();
            con.open();
            String sql = String.format("call spSanPham_insert('%s','%s','%d','%d','%s','%s');", MaSanPham, MaLoaiSanPham,SoLuong,GiaBan,MaNhaSanXuat,TenSanPham);
            CallableStatement st = (CallableStatement) con.getConn().prepareCall(sql);
            if(st.execute()== false)
            {
                result = 1;
            }
            con.close();
        }
        catch(SQLException ex)
        {
            //error
        }
        return result;
    }
    public int update(String MaSanPham, String MaLoaiSanPham, int SoLuong, int GiaBan, String MaNhaSanXuat, String TenSanPham)
    {
        int result = 0;
        try{
            dbconnect con = new dbconnect();
            con.open();
            String sql = String.format("call spSanPham_insert('%s','%s','%d','%d','%s','%s');", MaSanPham, MaLoaiSanPham,SoLuong,GiaBan,MaNhaSanXuat,TenSanPham);
            CallableStatement st = (CallableStatement) con.getConn().prepareCall(sql);
            if(st.execute()== false)
            {
                result = 1;
            }
            con.close();
        }
        catch(SQLException ex)
        {
            //error
        }
        return result;
    }
    public int delete(String MaSanPham)
    {
        int result = 0;
        try{
            dbconnect con = new dbconnect();
            con.open();
            String sql = String.format("call spSanPham_delete('%s');", MaSanPham);
            CallableStatement st = (CallableStatement) con.getConn().prepareCall(sql);
            if(st.execute()== false)
            {
                result = 1;
            }
            con.close();
        }
        catch(SQLException ex)
        {
            //error
        }
        return result;
    }
    public String  issue()
    {
        String MaSanPhamMoi = "null";
        try
        {
            dbconnect con = new dbconnect();
            con.open();
            String sql = String.format("call spSanPham_issue();");
            CallableStatement st = (CallableStatement) con.getConn().prepareCall(sql);
            st.execute();
            ResultSet rs = st.getResultSet();
            while (rs.next())
            {                
                MaSanPhamMoi = rs.getString("MaSanPhamMoi");
            }
            con.close();
        }
        catch(SQLException ex)
        {
            //error;
        }
        return MaSanPhamMoi;
    }
}
